﻿CREATE PROCEDURE [dbo].[usp_select_Reports_Alerts]
(
	@FaultID			varchar(50)		 = NULL
   ,@Application		varchar(50)      = NULL   
   ,@StartDate			varchar(25)      = NULL   
   ,@EndDate			varchar(25)      = NULL   
   ,@FaultCode			varchar(50)      = NULL   
   ,@FailureCategory	varchar(50)      = NULL
   ,@ErrorType			varchar(50)      = NULL 
   ,@MaxFaultSeverity	int				 = 4
   ,@StartRowIndex		int
   ,@MaximumRows		int
   ,@SortColumn			varchar(50)		 = ''
   ,@Debug              bit              = 0
)
AS

SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10)
DECLARE @ErrorCode         int

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: '


---- declare the stored procedure parameters
--/*
--DECLARE @FaultID		   varchar(50);
--DECLARE @Application       varchar(50);
--DECLARE @StartDate         datetime;
--DECLARE @EndDate           datetime;
--DECLARE @FaultCode         varchar(50);
--DECLARE @FailureCategory   varchar(50);
--DECLARE @ExceptionType     varchar(50);
--DECLARE @MinFaultSeverity  int;
--DECLARE @MaxFaultSeverity  int;
--
--DECLARE @Debug             bit;
---- now set them
--SELECT
--	@FaultID			   = NULL
--   ,@Application           = NULL    	
--   ,@StartDate             = NULL    	
--   ,@EndDate               = NULL    
--   ,@FaultCode             = NULL       
--   ,@FailureCategory       = NULL
--   ,@ExceptionType         = NULL 
--   ,@MinFaultSeverity      = NULL
--   ,@MaxFaultSeverity      = NULL
--   ,@Debug                 = 1;
--*/
--
---- Print parameters
--IF (@Debug > 0)
--BEGIN
--   PRINT @DebugPrefix + '@FaultID = ' + @FaultID
--   PRINT @DebugPrefix + '@Application = ' + @Application
--   PRINT @DebugPrefix + '@StartDate = ' + CAST(@StartDate AS varchar)
--   PRINT @DebugPrefix + '@EndDate = ' + CAST(@EndDate AS varchar)
--   PRINT @DebugPrefix + '@FaultCode = ' + @FaultCode
--   PRINT @DebugPrefix + '@FailureCategory = ' + @FailureCategory
--   PRINT @DebugPrefix + '@ErrorType = ' + @ErrorType
--END;
--
--
-----------------------------------------------------------------
----	Select the faults.
-----------------------------------------------------------------
--
--IF @SortColumn = '' BEGIN
--	SET @SortColumn = 'DateTime'
--END
--
--DECLARE @SqlStatement nvarchar(max)
--SET @SqlStatement = N' SELECT
--  FaultsWithRowNum.[FaultID]
--  ,FaultsWithRowNum.[NativeMessageID]
--  ,FaultsWithRowNum.[ActivityID]
--  ,FaultsWithRowNum.[Application]
--  ,FaultsWithRowNum.[Description]
--  ,FaultsWithRowNum.[ErrorType]
--  ,FaultsWithRowNum.[FailureCategory]
--  ,FaultsWithRowNum.[FaultCode]
--  ,FaultsWithRowNum.[FaultDescription]
--  ,FaultsWithRowNum.[FaultSeverity]
--  ,FaultsWithRowNum.[Scope]
--  ,FaultsWithRowNum.[ServiceInstanceID]
--  ,FaultsWithRowNum.[ServiceName]
--  ,FaultsWithRowNum.[FaultGenerator]
--  ,FaultsWithRowNum.[MachineName]
--  ,FaultsWithRowNum.[DateTime]
--  ,FaultsWithRowNum.[ExceptionMessage]
--  ,FaultsWithRowNum.[ExceptionType]
--  ,FaultsWithRowNum.[ExceptionSource]
--  ,FaultsWithRowNum.[ExceptionTargetSite]
--  ,FaultsWithRowNum.[ExceptionStackTrace]
--  ,FaultsWithRowNum.[InnerExceptionMessage]
--
--FROM
--(
--	SELECT 
--	   ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + N') AS Row
--	  ,f.[FaultID]
--	  ,f.[NativeMessageID]
--	  ,f.[ActivityID]
--	  ,f.[Application]
--	  ,f.[Description]
--	  ,f.[ErrorType]
--	  ,f.[FailureCategory]
--	  ,f.[FaultCode]
--	  ,f.[FaultDescription]
--	  ,f.[FaultSeverity]
--	  ,f.[Scope]
--	  ,f.[ServiceInstanceID]
--	  ,f.[ServiceName]
--	  ,f.[FaultGenerator]
--	  ,f.[MachineName]
--	  ,f.[DateTime]
--	  ,f.[ExceptionMessage]
--	  ,f.[ExceptionType]
--	  ,f.[ExceptionSource]
--	  ,f.[ExceptionTargetSite]
--	  ,f.[ExceptionStackTrace]
--	  ,f.[InnerExceptionMessage]
--
--	FROM dbo.Fault f 
--	WHERE 
--			 f.[FaultID] = ' + CASE WHEN @FaultID IS NULL THEN N'f.FaultID' ELSE N'''' + @FaultID + N'''' END + N'
--	   AND   f.[Application] = ' + CASE WHEN @Application IS NULL THEN N'f.[Application]' ELSE N'''' + @Application + N'''' END + N'
--	   AND   f.[FailureCategory] = ' + CASE WHEN @FailureCategory IS NULL THEN N'f.FailureCategory' ELSE N'''' + @FailureCategory + N'''' END + N'
--	   AND   f.ErrorType = ' + CASE WHEN @ErrorType IS NULL THEN N'f.ErrorType' ELSE N'''' + @ErrorType + N'''' END + N'
--	   AND   f.[FaultCode] =  ' + CASE WHEN @FaultCode IS NULL THEN N'f.[FaultCode]' ELSE N'''' + @FaultCode + N'''' END + N'
--	   AND   (dbo.udf_GetDateAtMidnight(f.[DateTime]) >= ' + CASE WHEN @StartDate = '-1' THEN N'dbo.udf_GetDateAtMidnight(f.[DateTime])' ELSE 'dbo.udf_GetDateAtMidnight(''' + @StartDate + ''')' END  + N' AND dbo.udf_GetDateAtMidnight(f.[DateTime]) <= ' + CASE WHEN @EndDate = '-1' THEN N'dbo.udf_GetDateAtMidnight(f.[DateTime])' ELSE 'dbo.udf_GetDateAtMidnight(''' + @EndDate + ''')' END + N')
--	   AND   f.[FaultSeverity] between 0 AND ' + cast(@MaxFaultSeverity as varchar(20)) + N'
--	
--) AS FaultsWithRowNum
--
--WHERE 
--	FaultsWithRowNum.Row >= (' + CONVERT(nvarchar(3), @StartRowIndex) + N' + 1)
--	AND FaultsWithRowNum.Row < (' + CONVERT(nvarchar(3), @StartRowIndex) + N' + ' + CONVERT(nvarchar(3), @MaximumRows) + N' + 1)'
--
--PRINT @SqlStatement
--EXEC (@SqlStatement)
--
--
--
--
-----------------------------------------------------------------
----	Select the total row count.
-----------------------------------------------------------------
--/*
--SELECT
--	@TotalRows = COUNT(FaultID)
--
--FROM dbo.Fault f 
--
--WHERE 
--		 f.[FaultID] = ISNULL(@FaultID, f.FaultID)
--   AND   f.[Application] = ISNULL(@Application, f.[Application])
--   AND   f.[FailureCategory] = ISNULL(@FailureCategory, f.FailureCategory)
--   AND   f.ExceptionType = ISNULL(@ExceptionType, f.ExceptionType)
--   AND   f.[FaultCode] = ISNULL(@FaultCode, f.[FaultCode])
--   AND   (
--			f.[DateTime] between CASE WHEN @StartDate IS NULL THEN f.[DateTime] ELSE CONVERT(varchar(25), @StartDate) END 
--			AND CASE WHEN @EndDate IS NULL THEN f.[DateTime] ELSE CONVERT(varchar(25), @EndDate) END
--		)
--   AND   (f.[FaultSeverity] between ISNULL(@MinFaultSeverity, f.[FaultSeverity]) AND ISNULL(@MaxFaultSeverity, f.[FaultSeverity]))
--
--*/
--
--
---- CHECK FOR ERROR
--SET @ErrorCode = @@Error;
--IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
--			
--RETURN 0;
--
---- Handles errors.
--ERROR_HANDLER:
--IF (@ErrorCode <> 0)
--BEGIN
--    IF (@Debug > 0) 
--		BEGIN
--			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
--			RETURN @ErrorCode
--		END
--
--    -- Don't return any evidence of what specific error occurred, i.e. 
--    -- we want to ward off potential hackers.
--    RETURN 1;
--END;
--
SET NOCOUNT OFF;
